import javax.sound.sampled.AudioInputStream;
import javax.swing.*;
import java.io.ByteArrayInputStream;
import java.io.File;
import java.io.IOException;
import java.io.InputStream;
import java.sql.*;
import java.util.ArrayList;
/**
 * Created by 蜡笔小新丶 on 2018/4/2.
 */
public class DAO {
    private String url;
    private String user;
    private String pword;
    Connection conn;
    Statement statement;

    public DAO() {
        this.url = "jdbc:mysql://localhost:3306/zxs?characterEncoding=utf8&useSSL=true";
        this.user = "root";
        pword = "123456";
    }

    private void creatConn() throws SQLException, ClassNotFoundException {
        Class.forName("com.mysql.jdbc.Driver");
        conn = DriverManager.getConnection(url, user, pword);
        statement = conn.createStatement();
    }

    public void addUser(User u) {
        try {
            creatConn();
            String sql = String.format(
                    "INSERT INTO people(name, num, passw, money) VALUES (?,?,?,?)");
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, u.getNC());
            pstmt.setString(2, u.getUserNum());
            pstmt.setString(3, u.getUserPassW());
            pstmt.setString(4, u.getMoney() + "");
            pstmt.execute();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public void Cash(String number, int money) {
        try {
            creatConn();
            String sql = String.format(
                    "UPDATE people SET money = '%d' WHERE num = '%s'", money, number);
            statement.executeUpdate(sql);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public boolean RestCheck(String m) {
        boolean YN = false;
        try {
            creatConn();
            String sql = String.format(
                    "SELECT num,name FROM people WHERE num = '%s' OR name = '%s'", m, m);
            ResultSet result = statement.executeQuery(sql);
            if (!result.next()) {
                YN = true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return YN;
    }

    public User LoginCheck(String Unum, String Password) {
        User u = null;
        try{
            creatConn();
            String sql = String.format(
                    "SELECT * FROM people WHERE num = '%s' and passw = '%s'", Unum, Password);
            ResultSet result = statement.executeQuery(sql);
            if (result.next()) {
                u = new User(result.getString(2), result.getString(1), result.getString(3), result.getInt(4));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return u;
    }

    public ArrayList<Jade> SelectShow() {
        ArrayList<Jade> jades = new ArrayList<>();
        try {
            creatConn();
            ResultSet result = statement.executeQuery("SELECT * FROM jade");
            while (result.next()) {
                Jade jade = toJade(result);
                jades.add(jade);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return jades;
    }

    private Jade toJade(ResultSet result) {
        Jade jade = null;
        try {
            creatConn();
            InputStream in = result.getBinaryStream(9);
            byte[] image = new byte[200000];
            in.read(image);
            String id = result.getString(1),
                    num = result.getString(2),
                    yname = result.getString(3),
                    from = result.getString(4),
                    type = result.getString(5),
                    money = result.getString(6),
                    telephone = result.getString(7),
                    remark = result.getString(8);

            jade = new Jade(id, num, yname, from, type, money, telephone, image, remark);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return jade;
    }

    public boolean addCom(Jade j) {
        boolean YN = false;
        try  {
            creatConn();
            String sql = String.format(
                    "INSERT INTO jade(ynum, num, nam, fr, type, money, telephone, image, remar) VALUES (?,?,?,?,?,?,?,?,?)");
            PreparedStatement pstmt = conn.prepareStatement(sql);
            ByteArrayInputStream bas = new ByteArrayInputStream(j.getImage());
            pstmt.setString(1, j.getID());
            pstmt.setString(2, j.getNum());
            pstmt.setString(3, j.getYname());
            pstmt.setString(4, j.getFrom());
            pstmt.setString(5, j.getType());
            pstmt.setString(6, j.getMoney());
            pstmt.setString(7, j.getTelephone());
            pstmt.setBinaryStream(8, bas, bas.available());
            pstmt.setString(9, j.getRemark());
            int a = pstmt.executeUpdate();
            if (a > 0)
                YN = true;
            bas.close();
            pstmt.close();
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return YN;
    }


    public void dropJade(String ID) {
        try {
            creatConn();
            statement.executeUpdate("delete from jade where ynum = " + ID);
        } catch (SQLException e) {
            e.printStackTrace();
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public void bought(buy m) {
        try {
            creatConn();
            String sql = String.format(
                    "INSERT INTO buy( num, ynum, shijian) VALUES (?,?,?)");
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, m.getNum());
            pstmt.setString(2, m.getYnum());
            pstmt.setString(3, m.getShijian());
            pstmt.execute();
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
    }

    public boolean cha(String JID){
        boolean result = false;
        try  {
                creatConn();
                ResultSet set = statement.executeQuery("SELECT ynum FROM buy WHERE ynum='"+JID+"'");
                if(set.next()){
                    result = true;
                }else
                    result = false;
        } catch (SQLException ex) {
            throw new RuntimeException(ex);
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        }
        return result;
    }
}
